
import time
from whiff import whiffenv
import urllib
import shutil

verbose = False

COOKIE_STRING = "ChatCookie"
CONNECTOR_STRING = "dbConnector"

TABLE_NAMES = "%(prefix)sRoom %(prefix)sInside %(prefix)sMessage %(prefix)sSpeaker"

# use this query to check that the database tables have been created.
TEST_QUERY = "select rid from %(p)sRoom where 1=0"

SQL_CREATE_TABLES = """

create table %(prefix)sRoom (
    rid varchar(20),
    descr varchar(200),
    is_foyer integer,
    max_keep_messages integer,
    refresh_ms integer,
    max_visible_messages integer,
    inactivity_timeout_secs integer,
    status varchar(100),
    changeTime float
);

create unique index %(prefix)sRoom_pk on %(prefix)sRoom(rid);

create table %(prefix)sSpeaker(
    nick varchar(20),
    detail varchar(200),
    lastinteraction float,
    status varchar(100),
    infoView varchar(100)
);

create unique index %(prefix)sSpeaker_pk on %(prefix)sSpeaker(nick);

create table %(prefix)sMessage (
    bynick varchar(20),
    toroom varchar(20),
    attime integer,
    said varchar(300)
);

create index %(prefix)sMessageRoom on %(prefix)sMessage(toroom);

create table %(prefix)sInside (
    nick varchar(20),
    rid varchar(20),
    joined float
);
create unique index %(prefix)sInside_pk on %(prefix)sInside(rid, nick);
create index %(prefix)sInsideRoom on %(prefix)sInside(rid);
create index %(prefix)sInsideNick on %(prefix)sInside(nick);
"""

class ChatRoomBase:
    "common functionality for all external storage methods"
    auto_populate = True
    def processEnvironment(self, env, prefix, nick, startRoom=None):
        # check if the nick is valid
        if not self.validateNick(nick):
            nick = None
            self.commit()
        # find the room for the nick
        (rid, roomInfo, view) = self.getRoomInfoForNick(nick, startRoom)
        if verbose:
            print "verbose: processEnvironment checking nick", nick
        if nick:
            env[prefix+".nick"] = nick
            env[prefix+".loggedIn"] = True
            self.touchSpeaker(nick)
        participants = self.getParticipantsList(rid)
        messages = self.getMessages(rid)
        roominfos = self.getRooms()
        rooms = [r["rid"] for r in roominfos]
        if verbose:
            print "verbose: getRoomInfoForNick", (prefix, nick)
            print "verbose: roomInfo", roomInfo
            print "verbose: participants", participants
            print "verbose: messages", messages
            print "verbose: rooms", rooms
            print "verbose: view", view
        envadd = {}
        envadd[prefix+"MenuBar.room"] = roomInfo
        envadd[prefix+"InfoList.participants"] = participants
        envadd[prefix+"InfoList.rooms"] = rooms
        envadd[prefix+"InfoList.view"] = view
        envadd[prefix+"MessageDisplay.messages"] = messages
        # xxx these only need to be set if there was a change from last time
        envadd[prefix+"ChangeMenuBar"] = True
        envadd[prefix+"ChangeInfoList"] = True
        envadd[prefix+"ChangeMessageDisplay"] = True
        if verbose:
            print "verbose: adding", envadd.keys()
        env.update(envadd)
        return env
    def getParticipantsList(self, rid):
        (status, dicts) = self.getParticipants(rid)
        return [ D["nick"] for D in dicts ]
    def getFoyer(self, startroom=None, retry=True):
        print "get foyer", (startroom, retry)
        roomdicts = self.getRooms()
        if startroom is not None:
            for r in roomdicts:
                if r["rid"]==startroom:
                    print "found startroom", (startroom, r)
                    return r
        for r in roomdicts:
            if r["is_foyer"]:
                print "default to foyer", r
                return r
        if retry and self.auto_populate and not roomdicts:
            # oops.  There are no rooms!
            # add a foyer and recursively try again.
            print "adding new foyer"
            self.addRoom("foyer", "automatically inserted default foyer", is_foyer=True)
            self.commit()
            return self.getFoyer(startroom, retry=False)
        raise ValueError, "no foyer found"

class NucularChatRooms(ChatRoomBase):
    def __init__(self, session, prefix=""):
        from nucular import nTableSpace
        self.prefix = prefix
        self.session = session
        tablespace = nTableSpace.TableSpace(session, prefix)
        tablespace.defTable("Room", "R", "rid")
        tablespace.defTable("Speaker", "S", "nick")
        tablespace.defTable("Message", "M", "toroom", "attime", "bynick")
        tablespace.defTable("Inside", "I", "rid", "nick")
        self.tablespace = tablespace
    def createTables(self):
        pass # don't need to do anything...
    def aggregate(self):
        self.session.aggregateRecent()
    def retrieve_dictionaries_gen(self, table, **kw):
        s = self.tablespace.selectByValuesDict(table, kw)
        return s()
    def get_dictionaries(self, table, **kw):
        return list( self.retrieve_dictionaries_gen(table, **kw) )
    def get_dictionary(self, table, **kw):
        count = 0
        result = None
        for D in self.retrieve_dictionaries_gen(table, **kw):
            result = D
            if count>0:
                raise ValueError, "too many results for "+repr(kw)
            count += 1
        if result is None:
            raise ValueError, "no result found for "+repr(kw)
        return result
    def commit(self):
        self.tablespace.store(lazy=False)
    def insertReplace(self, table, **kw):
        self.tablespace.deleteDict(table, kw)
        self.tablespace.insertDict(table, kw)
    def delete(self, table, **kw):
        self.tablespace.deleteDict(table, kw)
    def deleteAll(self, table, **kw):
        "delete any matching pattern"
        S = self.tablespace.selectByValuesDict(table, kw)
        S.delete()
    def update(self, table, **wherekw):
        "convenience: self.update(table, **where)(**set)"
        # note: it might be more readable to allow x.update(**where).set(**set)
        S = self.tablespace.selectByValuesDict(table, wherekw)
        def inner(**setkw):
            S.updateSetDict(setkw)
        return inner
    def addRoom(self, rid, descr, is_foyer=0, max_keep_messages=1000,
                refresh_ms=5000, max_visible_messages=50,
                inactivity_timeout_secs=60):
        #pr "Adding room", rid
        return self.insertReplace("Room", rid=rid, descr=descr, is_foyer=is_foyer, max_keep_messages=max_keep_messages,
                           inactivity_timeout_secs=inactivity_timeout_secs, status="new room", now=time.time())
    def addSpeaker(self, nick, detail, now=None):
        if verbose:  "verbose: Adding speaker", (nick, detail)
        return self.insertReplace("Speaker", nick=nick, detail=detail, lastinteraction=time.time(), status="new user", infoview="participants")
    def touchSpeaker(self, nick):
        self.update("Speaker", nick=nick)(lastinteraction=time.time())
        self.commit()
    def setView(self, nick, view):
        self.update("Speaker", nick=nick)(infoview=view)
        self.commit()
    def join(self, rid, nick):
        # xxxx should check that rid, nick are valid
        print "JOIN", rid, nick
        joined = self.get_dictionaries("Inside", nick=nick, rid=rid)
        for d in joined:
            print "CHECKING", d
            if d["rid"]==rid:
                print "IGNORE REJOIN", d
                return # ignore rejoin attempt
        # unjoin from anywhere else
        self.deleteAll("Inside", nick=nick)
        print "INSERTING Inside", (rid, nick)
        self.insertReplace("Inside", rid=rid, nick=nick, now=time.time())
        self.setRoomStatus(rid, "%s joined"%nick)
        self.commit()
    def unjoin(self, rid, nick):
        return self.deleteAll("Inside", nick=nick, rid=rid)
    def say(self, nick, rid, said):
        # xxxx should check validity of nick, rid, and nick inside rid
        return self.insertReplace("Message", bynick=nick, toroom=rid, attime=time.time(), said=said)
    def removeSpeaker(self, nick):
        self.deleteAll("Inside", nick=nick)
        self.delete("Speaker", nick=nick)
    def cleanRoom(self, rid):
        if verbose: print "Cleaning room", rid
        status = None
        rdata = self.get_dictionary("Room", rid=rid)
        print "rdata", rdata
        max_keep_messages = rdata["max_keep_messages"]
        inactivity_timeout_secs = rdata["inactivity_timeout_secs"]
        if max_keep_messages>0:
            # eliminate old message if too many
            messages = self.getMessages(rid, fixtime=False)
            if len(messages)>max_keep_messages:
                deletes = messages[:-int(max_keep_messages)]
                for message in deletes:
                    nick = message["bynick"]
                    #toroom = message["toroom"]
                    attime = message["attime"]
                    said = message["said"]
                    self.delete("Message", toroom=rid, bynick=nick, attime=attime)
                status = "deleted %s messages" % len(deletes)
        if inactivity_timeout_secs>0:
            # remove inactive members
            (memberstatus, members) = self.getParticipants(rid)
            lastdeletenick = None
            deletecount = 0
            now = time.time()
            if verbose: print "checking member activity"
            for memberdict in members:
                if verbose: print "   memberdict", memberdict
                lastinteraction = memberdict["lastinteraction"]
                print "lastinteraction", repr(lastinteraction), "now", repr(now)
                interval = now-lastinteraction
                if verbose: print "   interval=", interval
                if interval>inactivity_timeout_secs:
                    lastdeletenick = memberdict["nick"]
                    if verbose: print "Removing inactive speaker", lastdeletenick
                    self.removeSpeaker(lastdeletenick)
                    deletecount+=1
            if deletecount:
                if deletecount>1:
                    status = "deleted %s speakers including %s" % (deletecount, lastdeletenick)
                else:
                    status = "deleted speaker "+lastdeletenick
            else:
                #status = memberstatus
                pass
        if status:
            self.setRoomStatus(rid, status, False)
        self.commit()
        self.aggregate()
    def setRoomStatus(self, rid, status, copyToRoom=True):
        now = time.time()
        self.update("Room", rid=rid)(status=status, changeTime=time.time())
    def getRooms(self):
        result = self.get_dictionaries("Room")
        result = orderBy(result, "rid")
        return result
    def getRoom(self, rid):
        return self.get_dictionary("Room", rid=rid)
    def getParticipants(self, rid):
        result = self.get_dictionaries("Inside", rid=rid)
        for d in result:
            nick = d["nick"]
            speakerInfo = self.get_dictionary("Speaker", nick=nick)
            d.update(speakerInfo)
        result = orderBy(result, "nick")
        status = None
        if result:
            # get status of most recently active participant
            mostactive = result[-1]
            status = mostactive["status"]
        return (status, result)
    def getMessages(self, rid, fixtime=True):
        roomdict = self.getRoom(rid)
        result = self.get_dictionaries("Message", toroom=rid)
        result = orderBy(result, "attime")
        nresult = len(result)
        max_visible = int(roomdict.get("max_visible_messages", 0))
        if max_visible and nresult>max_visible:
            result = result[-max_visible:]
        # convert the attimes to readable strings
        if fixtime:
            for r in result:
                attime = r["attime"]
                r["attime"] = time.ctime(attime)
        return result
    def validateNick(self, nick):
        print "VALIDATENICK", nick
        test = self.get_dictionaries("Speaker", nick=nick)
        print "test is", test
        return len(test)==1
    def getRoomInfoForNick(self, nick, startRoom=None):
        # if the nick is in a room use that room
        # otherwise use any foyer and put the user there if the nick is valid
        print "getRoomInfo", (nick, startRoom)
        roomInfo = None
        view = "participants"
        if nick:
            nickData = self.get_dictionaries("Speaker", nick=nick)
            if nickData:
                print "found nick data", nickData
                view = nickData[0]["infoview"]
                insideData = self.get_dictionaries("Inside", nick=nick)
                print "found inside data", insideData
                if insideData:
                    startRoom = insideData[0]["rid"]
        roomInfo = self.getFoyer(startRoom)
        print "found room Info", roomInfo
        rid = roomInfo["rid"]
        # make sure the user has joined (noop if already joined)
        if nick:
            self.join(rid, nick)
        return (rid, roomInfo, view)
    

def orderBy(dicts, *attributes):
    L = [ [ d.get(a) for a in attributes ] + [d] for d in dicts ]
    L.sort()
    result = [ x[-1] for x in L ]
    return result

def kwDict(**kw):
    return kw.copy()

class ChatRooms(ChatRoomBase):
    "ChatRooms using SQL storage method"
    # flag: if set then add missing values to the database (such as at least one room) if needed
    def __init__(self, connection, tablePrefix="", sql_wildcard="?", create_table_edits=None):
        if tablePrefix is None:
            raise ValueError, "table prefix cannot be None"
        self.connection = connection
        self.tablePrefix = tablePrefix
        self.sql_wildcard = sql_wildcard
        self.create_table_edits = create_table_edits
    def tables_exist(self):
        "test whether the tables exist"
        try:
            self.get_dictionaries(TEST_QUERY)
        except:
            return False
        else:
            return True
    def autoCreateTables(self):
        if self.tables_exist():
            return True
        # try to create the tables
        self.createTables()
        # (no commit required)
        if not self.tables_exist():
            raise ValueError, "failed to create tables for chat instance"
    def cursor(self):
        return self.connection.cursor()
    def execute(self, statement, *arguments):
        c = self.cursor()
        #nargs = len(arguments)
        #subs = ((self.tablePrefix,)+(self.sql_wildcard,)*nargs)
        subs = {"p": self.tablePrefix, "w": self.sql_wildcard}
        stmt = str(statement % subs)
        if verbose:
            print "verbose: ...executing statement"
            print "verbose: ", repr(stmt)
            print "verbose: ... with arguments"
            print "verbose:", arguments
            print "verbose: ...done executing"
        c.execute(stmt, arguments)
        return c
    def retrieve_dictionaries_gen(self, statement, limit=None, *arguments):
        c = self.execute(statement, *arguments)
        if limit:
            results = c.fetchmany(limit)
        else:
            results = c.fetchall()
        description = c.description
        names = [ d[0].lower() for d in description ]
        for result in results:
            D = {}
            for (name, value) in zip(names, result):
                D[name] = value
            if verbose:
                print "verbose: yielding", D
            yield D
    def retrieve_dictionaries(self, statement, limit=None, *arguments):
        return list(self.retrieve_dictionaries_gen(statement, limit, *arguments))
    def get_dictionaries(self, statement, *arguments):
        result =  self.retrieve_dictionaries(statement, None, *arguments)
        if verbose:
            print "verbose: got", len(result), "dictionaries"
            for x in result:
                print "verbose: got dict", x
            print "verbose: end of dictionaries"
        return result
    def get_dictionary(self, statement, *arguments):
        dicts = self.retrieve_dictionaries(statement, 2, *arguments)
        if len(dicts)!=1:
            raise ValueError, "too many or too few results "+repr(len(dicts))
        return dicts[0]
    def commit(self):
        self.connection.commit()
    
    def createTables(self):
        if verbose: print "CREATING TABLES"
        prefixDict = {"prefix": self.tablePrefix}
        c = self.cursor()
        #pr "dropping tables and ignoring errors"
        tnames = TABLE_NAMES % prefixDict
        for table_name in tnames.split():
            drop_stmt = "drop table "+table_name
            if verbose: print "executing:", repr(drop_stmt)
            try:
                c.execute(drop_stmt)
            except:
                #pr "drop failed"
                pass
            else:
                #pr "drop succeeded"
                pass
        #pr "executing the create table declarations:"
        create_tables = SQL_CREATE_TABLES % prefixDict
        edits = self.create_table_edits
        if edits is not None:
            # edit the data types...
            create_tables = edits(create_tables)
        commands = create_tables.split(";")
        for command in commands:
            command = command.strip()
            if command:
                if verbose:
                    print "verbose: ....executing"
                    print command # verbose
                    print "verbose: ...."
                c.execute(command)
                if verbose: print "....execution succeeded"
        if verbose: print "committing updates"
        self.commit()
    def addRoom(self, rid, descr, is_foyer=0, max_keep_messages=1000,
                refresh_ms=5000, max_visible_messages=50,
                inactivity_timeout_secs=60):
        #pr "Adding room", rid
        stmt = """
        insert into %(p)sRoom
        (rid, descr, is_foyer, max_keep_messages, refresh_ms, max_visible_messages,
        inactivity_timeout_secs, status, changeTime) values
        (%(w)s, %(w)s, %(w)s, %(w)s, %(w)s, %(w)s, %(w)s, %(w)s, %(w)s)
        """
        now = time.time()
        status = "new room"
        return self.execute(stmt, rid, descr, is_foyer, max_keep_messages,
                            refresh_ms, max_visible_messages,
                            inactivity_timeout_secs, status, now)
    def addSpeaker(self, nick, detail, now=None):
        if verbose:  "verbose: Adding speaker", (nick, detail)
        stmt = """
        insert into %(p)sSpeaker
        (nick, detail, lastinteraction, status, infoview) values
        (%(w)s, %(w)s, %(w)s, %(w)s, %(w)s)
        """
        if now is None:
            now = time.time()
        #pr "adding speaker", (nick, detail, now, "new user", "participants")
        return self.execute(stmt, nick, detail, now, "new user", "participants")
    def touchSpeaker(self, nick):
        now = time.time()
        stmt= "update %(p)sSpeaker set lastinteraction=%(w)s where nick=%(w)s"
        self.execute(stmt, now, nick)
        self.commit()
    def setView(self, nick, view):
        stmt= "update %(p)sSpeaker set infoview=%(w)s where nick=%(w)s"
        self.execute(stmt, view, nick)
        self.commit()
    def join(self, rid, nick):
        if verbose: print "verbose: Speaker", nick, "joining", rid
        # check that room and nick are valid
        rtest = self.get_dictionary("select * from %(p)sRoom where rid=%(w)s", rid)
        ntest = self.get_dictionary("select * from %(p)sSpeaker where nick=%(w)s", nick)
        if not rtest or not ntest:
            #raise ValueError, "unreachable code"
            # ignore bogus call
            if verbose:
                print "verbose: bad nick or room", (nick, rid)
            return
        # ignore re-join
        rejoin_test = "select * from %(p)sInside where nick=%(w)s"
        #if self.get_dictionaries(rejoin_test, rid, nick):
        #    return
        joined = self.get_dictionaries(rejoin_test, nick)
        for d in joined:
            if d["rid"]==rid:
                # ignore rejoin attempt
                return
        # otherwise unjoin from room
        for d in joined:
            oldrid = d["rid"]
            self.unjoin(oldrid, nick)
        dels = """delete from %(p)sInside where nick=%(w)s"""
        if verbose:
            print "verbose: unjoining nick from previous rooms", repr(nick)
        # this should be redundant
        self.execute(dels, nick)
        stmt = """
        insert into %(p)sInside (rid, nick, joined)
        values (%(w)s, %(w)s, %(w)s)
        """
        now = time.time()
        self.setRoomStatus(rid, "%s joined" % nick)
        if verbose:
            print "verbose: joining", (rid, nick, now)
        return self.execute(stmt, rid, nick, now)
    def unjoin(self, rid, nick):
        testq = "select * from %(p)sInside where rid=%(w)s and nick=%(w)s"
        test = self.get_dictionaries(testq, rid, nick)
        if test:
            self.execute("delete from %(p)sInside where rid=%(w)s and nick=%(w)s",
                     rid, nick)
            self.setRoomStatus(rid, "%s left" % nick)
            self.commit()
    def say(self, nick, rid, said):
        if verbose: print "verbose: Speaker", nick, "says", repr(said), "to", rid
        # check that room and nick are valid
        rtest = self.get_dictionary("select * from %(p)sRoom where rid=%(w)s", rid)
        ntest = True
        if nick:
            ntest = self.get_dictionary("select * from %(p)sSpeaker where nick=%(w)s", nick)
        if not rtest or not ntest:
            raise ValueError, "unreachable code"
        stmt = """
        insert into %(p)sMessage (bynick, toroom, attime, said)
        values (%(w)s, %(w)s, %(w)s, %(w)s)
        """
        now = int(time.time())
        #self.setRoomStatus(rid, "%s spoke" % nick)
        return self.execute(stmt, nick, rid, now, said)
    def removeSpeaker(self, nick):
        #pr "Removing speaker", nick
        #self.execute("delete from %(p)sInside where nick=%(w)s", nick)
        rdicts = self.get_dictionaries("select rid from %(p)sInside where nick=%(w)s", nick)
        for rd in rdicts:
            rid = rd["rid"]
            self.unjoin(rid, nick)
        self.execute("delete from %(p)sSpeaker where nick=%(w)s", nick)
    def cleanRoom(self, rid):
        if verbose: print "Cleaning room", rid
        status = None
        rdata = self.get_dictionary("select * from %(p)sRoom where rid=%(w)s", rid)
        max_keep_messages = rdata["max_keep_messages"]
        inactivity_timeout_secs = rdata["inactivity_timeout_secs"]
        if max_keep_messages>0:
            # eliminate old message if too many
            messages = self.getMessages(rid, fixtime=False)
            if len(messages)>max_keep_messages:
                deletes = messages[:-int(max_keep_messages)]
                for message in deletes:
                    nick = message["bynick"]
                    #toroom = message["toroom"]
                    attime = message["attime"]
                    said = message["said"]
                    if verbose:
                        test = """
                        select * from %(p)sMessage where
                        toroom=%(w)s and
                        bynick=%(w)s and
                        attime=%(w)s and
                        said=%(w)s
                        """
                        dicts = self.get_dictionaries(test, rid, nick, attime, said)
                        if not dicts:
                            raise ValueError, "did not find match"+repr((message, rid, nick, attime, said))
                        if verbose:
                            print "verbose: deleting message", message, dicts
                    stmt = """
                        delete from %(p)sMessage where
                        toroom=%(w)s and
                        bynick=%(w)s and
                        attime=%(w)s and
                        said=%(w)s
                        """
                    self.execute(stmt, rid, nick, attime, said)
                    if verbose:
                        dicts = self.get_dictionaries(test, rid, nick, attime, said)
                        if dicts:
                            raise ValueError, "did not delete match", dicts
                        if verbose:
                            print "verbose: deleting message", message, dicts
                status = "deleted %s messages" % len(deletes)
        if inactivity_timeout_secs>0:
            # remove inactive members
            (memberstatus, members) = self.getParticipants(rid)
            lastdeletenick = None
            deletecount = 0
            now = time.time()
            if verbose: print "checking member activity"
            for memberdict in members:
                if verbose: print "   ", memberdict
                lastinteraction = memberdict["lastinteraction"]
                interval = now-lastinteraction
                if verbose: print "   interval=", interval
                if interval>inactivity_timeout_secs:
                    lastdeletenick = memberdict["nick"]
                    if verbose: print "Removing inactive speaker", lastdeletenick
                    self.removeSpeaker(lastdeletenick)
                    deletecount+=1
            if deletecount:
                if deletecount>1:
                    status = "deleted %s speakers including %s" % (deletecount, lastdeletenick)
                else:
                    status = "deleted speaker "+lastdeletenick
            else:
                #status = memberstatus
                pass
        if status:
            self.setRoomStatus(rid, status, False)
        self.commit()
    def setRoomStatus(self, rid, status, copyToRoom=True):
        # update the room status
        if verbose:
            print "verbose: setting status for room", (rid, status)
        now = time.time()
        if copyToRoom:
            self.say("", rid, status)
        self.execute("update %(p)sRoom set status=%(w)s, changeTime=%(w)s where rid=%(w)s",
                     status, now, rid)
    def getRooms(self):
        #pr "Getting rooms"
        return self.get_dictionaries("select * from %(p)sRoom order by rid")
    def getRoom(self, rid):
        return self.get_dictionary("select * from %(p)sRoom where rid=%(w)s", status)
    def getParticipants(self, rid):
        #pr "Getting participants"
        stmt = """
        select i.nick, i.joined, s.detail, s.lastinteraction, s.status
        from %(p)sInside as i, %(p)sSpeaker as s
        where i.nick=s.nick and i.rid=%(w)s
        order by i.nick
        """
        result = self.get_dictionaries(stmt, rid)
        status = None
        if result:
            # get status of most recently active participant
            mostactive = result[-1]
            status = mostactive["status"]
        return (status, result)
    def getMessages(self, rid, fixtime=True):
        #pr "Getting Messages"
        stmt = """
            select *
            from %(p)sMessage
            where toroom=%(w)s
            order by attime
        """
        result = self.get_dictionaries(stmt, rid)
        roomInfo = """
            select *
            from %(p)sRoom
            where rid=%(w)s
        """
        roomdict = self.get_dictionary(roomInfo, rid)
        max_visible = int(roomdict["max_visible_messages"])
        nresult = len(result)
        if nresult>max_visible:
            result = result[-max_visible:]
        return result
    def validateNick(self, nick):
        testQuery = "select * from %(p)sSpeaker where nick=%(w)s"
        testData = self.get_dictionaries(testQuery, nick)
        return len(testData)==1
    def getRoomInfoForNick(self, nick, startRoom=None):
        # if the nick is in a room use that room
        # otherwise use any foyer and put the user there if the nick is valid
        roomInfo = None
        view = "participants"
        if nick:
            nickQuery = "select * from %(p)sSpeaker where nick=%(w)s"
            nickData = self.get_dictionaries(nickQuery, nick)
            if nickData:
                view = nickData[0]["infoview"]
                insideQuery = "select * from %(p)sInside where nick=%(w)s"
                insideData = self.get_dictionaries(insideQuery, nick)
                if insideData:
                    startRoom = insideData[0]["rid"]
        roomInfo = self.getFoyer(startRoom)
        rid = roomInfo["rid"]
        # make sure the user has joined (noop if already joined)
        if nick:
            self.join(rid, nick)
        return (rid, roomInfo, view)

def cookieName(env, identifier=""):
    #result = env[whiffenv.FULL_CGI_PREFIX]+identifier+"ChatCookie"
    result = whiffenv.getName(env, identifier+COOKIE_STRING)
    if verbose:
        print "verbose: cookie name is", result
    return str(result)

def registerConnector(ConnectorFunction, env):
    env = env.copy()
    whiffenv.setId(env, CONNECTOR_STRING, ConnectorFunction)
    return env

def getConnection(env):
    connectorFunction = whiffenv.getId(env, CONNECTOR_STRING)
    connection = connectorFunction()
    return connection

def getCookieValue(env, identifier=""):
    import Cookie
    result = None
    cookie_name = cookieName(env, identifier)
    cookie_header = env.get("HTTP_COOKIE")
    if verbose:
        print "verbose: cookie header is", repr(cookie_header)
    if cookie_header is not None:
        c = Cookie.SimpleCookie(cookie_header)
        if c.has_key(cookie_name):
            result =  c[cookie_name].value
            result = urllib.unquote(result)
    if verbose:
        print "verbose: returning cookie value", result
    return result

def mySqlConnection(credentialsPath, prefix="myPrefix"):
    """
    Get Mysql login connection credentials from credentialsPath in form "host:user:password:database".
    Return chatrooms connection.
    """
    import MySQLdb
    credentialsFile = file(credentialsPath, "r")
    credentialsString = credentialsFile.read().strip()
    [host, user, passwd, db] = credentialsString.split(":")
    connection = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
    return ChatRooms(connection, prefix, "%s", mysql_create_table_edits)

def mysql_create_table_edits(createTableText):
    "force mysql to use double precision floats"
    createTableText = createTableText.replace("float", "float(53)")
    return createTableText

def getNucularConnection(archivePath, prefix, create=False):
    import os
    if create and os.path.exists(archivePath):
        shutil.rmtree(archivePath)
        os.mkdir(archivePath)
    from nucular import Nucular # this functionality requires Nucular (packaged separately)
    session = Nucular.Nucular(archivePath)
    if create:
        session.create()
    chatrooms = NucularChatRooms(session, prefix)
    return chatrooms
    
# testing stuff...

def test_gadfly_connection(prefix="myPrefix", database="testchat", directory="/tmp/testdatabase"):
    import gadfly
    connection = gadfly.gadfly(database, directory)
    return ChatRooms(connection, prefix, "?")

def test_with_gadfly(directory="/tmp/testdatabase", prefix="myPrefix", database="testchat"):
    print "test: initializing and exercising module features using gadfly database at", directory
    import os
    import gadfly # test requires gadfly
    if not os.path.exists(directory):
        os.mkdir(directory)
    connection = gadfly.gadfly()
    connection.startup(database, directory)
    chatrooms = ChatRooms(connection, prefix, "?")
    return test_chatrooms(chatrooms)

def test_with_nucular(directory="/usr/local/apache2/wsgi/chatdb", prefix="nP"):
    chatrooms = getNucularConnection(directory, prefix, create=True)
    return test_chatrooms(chatrooms)

def test_chatrooms(chatrooms):
    chatrooms.createTables()
    chatrooms.commit()
    chatrooms.addRoom("dogs", "people who like dogs", 1, max_keep_messages=10)
    chatrooms.addRoom("cats", "people who like cats", 1)
    chatrooms.addSpeaker("george", "castanza")
    chatrooms.addSpeaker("jerry", "seinfeld")
    chatrooms.addSpeaker("norm", "from cheers", 0) # make norm very inactive
    for sp in ("george", "jerry", "norm"):
        for rm in ("dogs", "cats"):
            chatrooms.join(rm, sp)
    chatrooms.say("jerry", "dogs", "hi george")
    chatrooms.say("george", "dogs", "yea what?")
    chatrooms.say("norm", "cats", "cats are annoying")
    chatrooms.say("norm", "dogs", "dogs are annoying too")
    #chatrooms.unjoin("cats", "george")
    chatrooms.removeSpeaker("jerry")
    chatrooms.cleanRoom("cats")
    print "process env"
    for pair in chatrooms.processEnvironment({}, "Goober", None).items():
        print pair
    chatrooms.commit()
    print "execution complete with no exceptions"
    return chatrooms

if __name__=="__main__":
    #test_with_gadfly()
    test_with_nucular()

